{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4516e5e3-0807-495b-a765-7e19b039cd7e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://hbu:********@127.0.0.1:2345/hbu\n",
      "环境变量加载完成！\n",
      "数据库连接引擎创建成功！\n"
     ]
    }
   ],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "# 加载环境变量\n",
    "load_dotenv()\n",
    "\n",
    "# 从环境变量获取数据库连接信息，如果不存在则使用默认值\n",
    "driver=os.getenv('DRIVER', 'postgresql')\n",
    "user=os.getenv('PGUSER', None)\n",
    "password=os.getenv('PGPASSWORD', None)\n",
    "host=os.getenv('PGHOST', None)\n",
    "port=os.getenv('PGPORT', None)\n",
    "database=os.getenv('PGDATABASE', 'postgres')\n",
    "\n",
    "# schema is used for postgres, similiar with database level in MySQL\n",
    "schema=os.getenv('SCHEMA',\"public\")\n",
    "\n",
    "DATABASE_URL = f\"{driver}://{user}:{password}@{host}:{port}/{database}\"\n",
    "\n",
    "if user is not None and password is not None:\n",
    "    print(f\"{driver}://{user}:********@{host}:{port}/{database}\")\n",
    "else:\n",
    "    print('非法的数据库连接URL')\n",
    "    sys.exit(1)\n",
    "print('环境变量加载完成！')\n",
    "\n",
    "# 创建数据库连接引擎\n",
    "try:\n",
    "    engine = create_engine(DATABASE_URL)\n",
    "    print('数据库连接引擎创建成功！')\n",
    "except Exception as e:\n",
    "    print(f'创建数据库连接引擎失败: {e}')\n",
    "    raise\n",
    "# 从PostgreSQL数据库读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "682f626b-f3eb-4747-8aad-61df161a0b44",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "成功读取数据，共398条记录\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index(['mpg', 'cylinders', 'displacement', 'horsepower', 'weight',\n",
       "       'acceleration', 'model year', 'origin', 'car name'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try:\n",
    "    # 查询public模式下的patient_data表\n",
    "    query = f'SELECT * FROM public.\"auto-mpg\"'\n",
    "    data = pd.read_sql(query, engine)\n",
    "    print(f'成功读取数据，共{len(data)}条记录')\n",
    "except Exception as e:\n",
    "    print(f'读取数据失败: {e}')\n",
    "data.columns\n",
    "data.drop(columns=['id'],inplace=True)\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "89241406-595d-41ad-a42f-0d08ce8c748c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据集的前五行:\n",
      "    mpg  cylinders  displacement horsepower  weight  acceleration  model year  \\\n",
      "0  18.0        8.0           NaN        130  3504.0          12.0        70.0   \n",
      "1  15.0        8.0         350.0        165  3693.0          11.5        70.0   \n",
      "2  18.0        8.0         318.0        150  3436.0          11.0        70.0   \n",
      "3  16.0        8.0         304.0        150  3433.0          12.0        70.0   \n",
      "4  17.0        8.0         302.0        140  3449.0          10.5        70.0   \n",
      "\n",
      "   origin                   car name  \n",
      "0     1.0  chevrolet chevelle malibu  \n",
      "1     1.0          buick skylark 320  \n",
      "2     1.0         plymouth satellite  \n",
      "3     1.0              amc rebel sst  \n",
      "4     1.0                ford torino  \n",
      "mpg             float64\n",
      "cylinders       float64\n",
      "displacement    float64\n",
      "horsepower       object\n",
      "weight          float64\n",
      "acceleration    float64\n",
      "model year      float64\n",
      "origin          float64\n",
      "car name         object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# 加载数据集并显示数据集的前五行 1分\n",
    "# data = pd.read_csv('auto-mpg.csv')\n",
    "print(\"数据集的前五行:\")\n",
    "print(data.head())\n",
    "\n",
    "# 显示每一列的数据类型\n",
    "print(data.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bb840be1-5405-48bc-9e2a-6ffda47e4d8d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "检查缺失值:\n",
      "mpg             0\n",
      "cylinders       1\n",
      "displacement    3\n",
      "horsepower      1\n",
      "weight          2\n",
      "acceleration    1\n",
      "model year      1\n",
      "origin          0\n",
      "car name        0\n",
      "dtype: int64\n",
      "float64\n",
      "\n",
      "检查清洗后的缺失值:\n",
      "mpg             0\n",
      "cylinders       0\n",
      "displacement    0\n",
      "horsepower      0\n",
      "weight          0\n",
      "acceleration    0\n",
      "model year      0\n",
      "origin          0\n",
      "car name        0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# 检查缺失值并删除缺失值所在的行  2分\n",
    "print(\"\\n检查缺失值:\")\n",
    "print(data.isna().sum())  \n",
    "data = data.dropna()\n",
    "\n",
    "# 将 'horsepower' 列转换为数值类型，并（删除）处理转换中的异常值 1分\n",
    "data['horsepower'] = pd.to_numeric(data['horsepower'], errors='coerce')\n",
    "data = data.dropna()\n",
    "\n",
    "# 显示每一列的数据类型\n",
    "print(data.horsepower.dtypes)\n",
    "\n",
    "# 检查清洗后的缺失值\n",
    "print(\"\\n检查清洗后的缺失值:\")\n",
    "print(data.isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "20659500-d5a2-4252-a37a-40a5e78a0909",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import StandardScaler\n",
    "# 对数值型数据进行标准化处理 1分\n",
    "numerical_features = ['displacement', 'horsepower', 'weight', 'acceleration']\n",
    "scaler = StandardScaler()\n",
    "data[numerical_features] = scaler.fit_transform(data[numerical_features])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0b26dbbb-698f-43e0-8158-9f9c2cdd1b54",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "清洗后的数据已保存到 2.1.1_cleaned_data.csv\n"
     ]
    }
   ],
   "source": [
    "\n",
    "from sklearn.model_selection import train_test_split\n",
    "# 选择特征、自变量和目标变量 2分\n",
    "selected_features = ['cylinders', 'displacement', 'horsepower', 'weight',\n",
    "       'acceleration', 'model year', 'origin']\n",
    "X = data[selected_features]\n",
    "y = data['mpg']\n",
    "\n",
    "# 划分数据集为训练集和测试集（训练集占8成） 1分\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)\n",
    "\n",
    "\n",
    "# 将特征和目标变量合并到一个数据框中\n",
    "cleaned_data = X.copy()\n",
    "cleaned_data['mpg'] = y\n",
    "# 保存清洗和处理后的数据（不存储额外的索引号） 1分\n",
    "cleaned_data.to_csv('2.1.1_cleaned_data.csv', index=False)\n",
    "\n",
    "# 打印消息指示文件已保存\n",
    "print(\"\\n清洗后的数据已保存到 2.1.1_cleaned_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "13dc7032-35b8-48f9-b313-973d3ff79dad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "382"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_data.to_sql(\n",
    "    name='2.1.1_cleaned_data',\n",
    "    con=engine,\n",
    "    index=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65280703-71c2-4668-88ce-862f4e6c1085",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
